﻿///***************************************************************
/// 시  스  템 : Updms.Crd.Dac
/// 단위시스템 : CRD
/// 계      층 : Data Access Layer
/// 프로그램명 : Crd_Dac_UPDMS_CRD_REPORT_Crd2019r
/// 파  일  명 : Crd_Dac_UPDMS_CRD_REPORT_Crd2019r.cs
/// 설      명 : 기간별사용분석표
///***************************************************************
/// 수정ID 일자       이름     내용
///---------------------------------------------------------------
/// CH001  2011-02-09 송시명   최초 작성
///***************************************************************
using System;
using System.Data;
using System.EnterpriseServices;
using System.Reflection;
using System.Runtime.InteropServices;

using SMFramework.Data;
using System.Text;
using SMFramework.Utility;

namespace Updms.Crd.Dac
{
	[ComVisible(true)]
	[Transaction(TransactionOption.Supported)]
	[JustInTimeActivation(true)]
	public class Crd_Dac_UPDMS_CRD_REPORT_Crd2019r : Base.AppFramework.DacBase
	{
		public Crd_Dac_UPDMS_CRD_REPORT_Crd2019r()
		{
		}

		#region NT [GetRegionalUsingAnalysisList]
		/// <summary>
		/// 기간별 사용분석표
		/// </summary>
		/// <param name="lo_dp"></param>
		/// <returns></returns>
		[AutoComplete]
		public DataSet GetRegionalUsingAnalysisList(DataPack lo_dp)
		{
			string ls_curr_method
				= MethodBase.GetCurrentMethod().DeclaringType.ToString() + " - " + MethodBase.GetCurrentMethod().ToString();

			StringBuilder sb = new StringBuilder();

			string ls_card_cd = lo_dp["ls_card_cd"].ToString();
			string ls_dt_from = lo_dp["ls_dt_from"].ToString();
			string ls_dt_to = lo_dp["ls_dt_to"].ToString();
			string ls_lang_set = lo_dp["ls_lang_set"].ToString();
			bool lb_is_excel = Convert.ToBoolean(lo_dp["ls_is_excel"].ToString());

			string monthList = DateUtil.GetDiffMonthList(ls_dt_from, ls_dt_to);
			string[] monthLists = monthList.Split(',');
			string ls_total_amt = "";
			string in_query = string.Empty;

			sb.Append("SELECT View_Order,\n");
			sb.Append("       Account_Nm,\n");
			
			for (int i = 0; i < monthLists.Length; i++)
			{
				//컬럼
				string ym = monthLists[i];

				if (lb_is_excel)
				{
					sb.Append("ISNULL([" + ym + "], 0) AS '" + ym + "',\n");
				}
				else
				{
					sb.Append("'       <span ' + CASE WHEN View_Order <> 'BB006' AND [" + ym + "] > 0 THEN '");
					sb.Append("onclick=\"javascript:viewDetailList(''" + ls_card_cd + "'',''' + Account + ''',''" + ym + "'');\">' ELSE '");
					sb.Append("onclick=\"javascript:void(0);\">' END + dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([" + ym + "], 0))) + '</span>' AS '" + ym + "',\n");
				}
				//총액
				ls_total_amt += "ISNULL([" + monthLists[i] + "], 0)+";

				in_query += "[" + ym + "]" + ",";	//PIVOT의 IN절
			}

			in_query = in_query.Substring(0, in_query.LastIndexOf(","));

			if (lb_is_excel)
			{
				ls_total_amt = ls_total_amt.Remove(ls_total_amt.Length - 1, 1) + " AS 'Total'";				
			}
			else
			{
				ls_total_amt = "dbo.ufn_UPDMS_Convert_Money(" + ls_total_amt.Remove(ls_total_amt.Length - 1, 1) + ") AS 'Total'";
			}

			sb.Append(ls_total_amt + "\n");
			sb.Append("  FROM (\n");
			sb.Append("       SELECT dbo.ufn_UPDMS_Get_Code_View_Order(t.Account, 'CRD') AS View_Order,\n");
			sb.Append("              Paymnt_Dt,\n");
			sb.Append("              t.Account,\n");
			sb.Append("              t.Account_Nm,\n");
			sb.Append("              t.Amount\n");
			sb.Append("         FROM (\n");
			sb.Append("              SELECT crd.Paymnt_Dt,\n");
			sb.Append("                     crd.Account,\n");
			sb.Append("                     MAX(crd.Account_Nm) AS Account_Nm,\n");
			sb.Append("                     SUM(ISNULL(crd.Amount,0)) AS Amount\n");
			sb.Append("                FROM (--일시불\n");
			sb.Append("                     SELECT LEFT(c.Paymnt_Dt, 7) AS Paymnt_Dt,\n");
			sb.Append("                            c.Account,\n");
			sb.Append("                            dbo.ufn_UPDMS_Get_Code_Name(c.Account, 'CRD', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("                            SUM(ISNULL(c.Amount, 0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_ITEM_BIZ c WITH(NOLOCK)\n");
			sb.Append("                      WHERE c.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma('" + ls_card_cd + "'))\n");
			sb.Append("                        AND LEFT(c.Paymnt_Dt, 7) BETWEEN '" + ls_dt_from + "' AND '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(c.Paymnt_Dt, 7), c.Account\n");
			sb.Append("                     UNION ALL\n");
			sb.Append("                     --할부\n");
			sb.Append("                     SELECT LEFT(ucir.Paymnt_Dt, 7) AS Paymnt_Dt,\n");
			sb.Append("                            ucib.Account,\n");
			sb.Append("                            dbo.ufn_UPDMS_Get_Code_Name(ucib.Account, 'CRD', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("                            SUM(ISNULL(ucir.Ins_Month_Amt,0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_INSTALLMENT_REPAY AS ucir WITH(NOLOCK)\n");
			sb.Append("                       JOIN UPDMS_CRD_INSTALLMENT_BIZ AS ucib WITH(NOLOCK) ON ucir.Ins_Cd = ucib.Ins_Cd\n");
			sb.Append("                      WHERE ucir.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma('" + ls_card_cd + "'))\n");
			sb.Append("                        AND LEFT(ucir.Paymnt_Dt, 7) BETWEEN '" + ls_dt_from + "' AND '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(ucir.Paymnt_Dt, 7), ucib.Account\n");
			sb.Append("                     ) crd\n");
			sb.Append("               GROUP BY crd.Paymnt_Dt, crd.Account\n");
			sb.Append("              UNION ALL\n");
			sb.Append("              --총계\n");
			sb.Append("              SELECT crd.Paymnt_Dt,\n");
			sb.Append("                     'BB006' AS Account,\n");
			sb.Append("                     dbo.ufn_UPDMS_Get_Code_Name('BB006', 'CRD', '" + ls_lang_set + "') AS Account_Nm,\n");
			sb.Append("                     SUM(ISNULL(crd.Amount,0)) AS Amount\n");
			sb.Append("                FROM (--일시불\n");
			sb.Append("                     SELECT LEFT(c.Paymnt_Dt, 7) AS Paymnt_Dt,\n");
			sb.Append("                            SUM(ISNULL(c.Amount, 0)) AS Amount\n");
			sb.Append("                       FROM UPDMS_CRD_ITEM_BIZ c WITH(NOLOCK)\n");
			sb.Append("                      WHERE c.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma('" + ls_card_cd + "'))\n");
			sb.Append("                        AND LEFT(c.Paymnt_Dt, 7) BETWEEN '" + ls_dt_from + "' AND '" + ls_dt_to + "'\n");
			sb.Append("                      GROUP BY LEFT(c.Paymnt_Dt, 7)\n");
			sb.Append("                      UNION ALL\n");
			sb.Append("                      --할부\n");
			sb.Append("                      SELECT LEFT(ucir.Paymnt_Dt, 7) AS Paymnt_Dt,\n");
			sb.Append("                             SUM(ISNULL(ucir.Ins_Month_Amt,0)) AS Amount\n");
			sb.Append("                        FROM UPDMS_CRD_INSTALLMENT_REPAY AS ucir WITH(NOLOCK)\n");
			sb.Append("                        JOIN UPDMS_CRD_INSTALLMENT_BIZ AS ucib WITH(NOLOCK) ON ucir.Ins_Cd = ucib.Ins_Cd\n");
			sb.Append("                       WHERE ucir.Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma('" + ls_card_cd + "'))\n");
			sb.Append("                         AND LEFT(ucir.Paymnt_Dt, 7) BETWEEN '" + ls_dt_from + "' AND '"+ ls_dt_to + "'\n");
			sb.Append("                       GROUP BY LEFT(ucir.Paymnt_Dt, 7)\n");
			sb.Append("                     ) crd\n");
			sb.Append("               GROUP BY crd.Paymnt_Dt\n");
			sb.Append("              ) t\n");
			sb.Append("       ) pv\n");
			sb.Append(" PIVOT ( SUM(pv.Amount) FOR pv.Paymnt_Dt IN (" + in_query + ") ) AS pvt\n");
			sb.Append(" ORDER BY View_Order, Account");

			//Base.AppFramework.SMEventLog.WriteDataLog("GetRegionalUsingAnalysisList", sb.ToString());

			DataSet ds = new DataSet();

			return _agent.Fill(sb.ToString(), "UPDMS_CRD_REPORT", ds, null, CommandType.Text, ls_curr_method);
		}

		#endregion

	}
}
